﻿



--////////////////////////////////////////KHUYEN MAI//////////////////////////////////////////
CREATE PROCEDURE sp_InsertKhuyenMai
	@Ma_KhuyenMai nchar (3),
	@Ma_MonAn nchar (3),
	@PhanTramGiam int,
	@NgayBatDau datetime,
	@NgayKetThuc datetime
AS
BEGIN TRAN
	DECLARE  @NBD datetime
	DECLARE @NKT datetime
	DECLARE @cur CURSOR
	DECLARE @FLAG int
	SET @FLAG=0
	SET @cur= CURSOR FOR (SELECT NgayBatDau,NgayKetThuc FROM KhuyenMai WHERE Ma_MonAn=@Ma_MonAn)
	OPEN @CUR
	FETCH NEXT FROM @cur INTO @NBD,@NKT
	WHILE @@fetch_status = 0
	BEGIN
		IF((@NgayBatDau between @NBD and @NKT)OR (@NgayKetThuc between @NBD and @NKT) OR ((@NBD between @NgayBatDau and @NgayKetThuc) and (@NKT between @NgayBatDau and @NgayKetThuc)) )
		BEGIN
				SET @FLAG=1
		END
		
		FETCH NEXT FROM @cur INTO @NBD,@NKT
	END
	
	waitfor delay '0:0:05'

	IF(@FLAG=0)
	BEGIN
		INSERT INTO KhuyenMai VALUES(@Ma_KhuyenMai,@Ma_MonAn,@PhanTramGiam,@NgayBatDau,@NgayKetThuc)
	END
COMMIT TRAN 
GO
----EXEC sp_InsertKhuyenMai '015','004',10,'2013-07-03 00:00:00.000','2013-07-09 00:00:00.000'


--Update Thông tin khuyễn mãi
CREATE PROCEDURE sp_UpdateKhuyenMaiById
	@Ma_KhuyenMai nchar (3),
	@Ma_MonAn nchar (3),
	@PhanTramGiam int,
	@NgayBatDau datetime,
	@NgayKetThuc datetime
AS
BEGIN
BEGIN TRAN
	Update KhuyenMai SET Ma_MonAn=@Ma_MonAn,PhanTramGiam=@PhanTramGiam,NgayKetThuc=@NgayKetThuc,NgayBatDau=@NgayBatDau WHERE Ma_KhuyenMai=@Ma_KhuyenMai
COMMIT TRAN
END
GO

--EXEC sp_UpdateKhuyenMaiById '001','005',15,'2013-05-30 00:00:00.000','2013-06-30 00:00:00.000'



CREATE PROCEDURE sp_SelectKhuyenMaiAll @soKM int out
AS
BEGIN
BEGIN TRAN
	
	set @soKM = (select count(*) from KhuyenMai)

	SELECT Ma_KhuyenMai,Ma_MonAn,PhanTramGiam,NgayBatDau,NgayKetThuc FROM KhuyenMai
	
COMMIT TRAN
END
GO

--EXEC sp_SelectKhuyenMaiAll






CREATE PROCEDURE sp_SelectKhuyenMaiById
	@Ma_KhuyenMai nchar (3)
AS
BEGIN
BEGIN TRAN
	SELECT Ma_MonAn,PhanTramGiam,NgayBatDau,NgayKetThuc FROM KhuyenMai WHERE Ma_KhuyenMai=@Ma_KhuyenMai
COMMIT TRAN
END
GO

--EXEC sp_SelectKhuyenMaiById '002'





CREATE PROCEDURE sp_DeleteKhuyenMaiById
	@Ma_KhuyenMai nchar (3)
AS
BEGIN
BEGIN TRAN
	DELETE FROM KhuyenMai WHERE Ma_KhuyenMai=@Ma_KhuyenMai
COMMIT TRAN
END
GO

--EXEC sp_DeleteKhuyenMaiById '004'




CREATE PROCEDURE sp_CreateKhuyenMaiId
AS
BEGIN
		DECLARE @max NCHAR(3)
		SELECT @max = max(Ma_KhuyenMai) FROM KhuyenMai
		IF(@max is NULL)
			SELECT '001'
		ELSE
			BEGIN
			SET @max =  cast(@max AS INT)+1
			SELECT cast(@max AS NCHAR(3))
			END
END

--EXEC sp_CreateKhuyenMaiId


--////////////////////////////////////////////DAT HANG/////////////////////////////////////////
CREATE PROCEDURE sp_InsertPhieuDat
	@Ma_PD nchar(3),
	@NV_Dat nchar(3),
	@Ten_KH nvarchar(50),
	@DiaChi nvarchar(100),
	@DienThoai nvarchar(15),
	@NgayDat datetime,
	@ThanhTien  float,
	@TinhTrang  int,
	@GhiChu nvarchar(100)
AS
BEGIN
BEGIN TRAN
	INSERT INTO PhieuDat  VALUES(@Ma_PD,@NV_Dat,@Ten_KH,@DiaChi,@DienThoai,@NgayDat,@ThanhTien,@TinhTrang,@GhiChu)
COMMIT TRAN
END
GO

--EXEC sp_InsertPhieuDat '011','005',N'Lê Văn Lách Luât',N'9999 Hai Bà Trưng','37477755','2013-05-09 00:00:00.000',236000,1,N'Ưho knớ'

CREATE PROCEDURE sp_UpdatePhieuDatById
	@Ma_PD nchar(3),
	@NV_Dat nchar(3),
	@Ten_KH nvarchar(50),
	@DiaChi nvarchar(100),
	@DienThoai nvarchar(15),
	@NgayDat datetime,
	@ThanhTien  float,
	@TinhTrang  int,
	@GhiChu nvarchar(100)
AS
BEGIN
BEGIN TRAN
	UPDATE  PhieuDat SET NV_Dat=@NV_Dat,Ten_KH=@Ten_KH,DiaChi=@DiaChi,DienThoai=@DienThoai,NgayDat=@NgayDat,ThanhTien=@ThanhTien,TinhTrang=@TinhTrang,GhiChu=@GhiChu WHERE Ma_PD=@Ma_PD
COMMIT TRAN
END
GO

--EXEC sp_UpdatePhieuDatById '011','005',N'Lê Văn Lách Luât',N'9999999 Hai Bà Trưng','37477755','2013-05-09 00:00:00.000',236000,1,N'Ưho knớ' 

CREATE PROCEDURE sp_DeletePhieuDatById
	@Ma_PD nchar(3)
AS
BEGIN
BEGIN TRAN
	DELETE FROM PhieuDat WHERE Ma_PD=@Ma_PD
COMMIT TRAN
END
GO

--EXEC sp_DeletePhieuDatById '011'

CREATE PROCEDURE sp_SelectPhieuDatAll
AS
BEGIN
BEGIN TRAN
	SELECT  Ma_PD,NV_Dat,Ten_KH,DiaChi,DienThoai,NgayDat,ThanhTien,TinhTrang,GhiChu FROM PhieuDat
COMMIT TRAN
END
GO
--EXEC sp_SelectPhieuDat

CREATE PROCEDURE sp_SelectPhieuDatById
	@Ma_PD nchar(3)
AS
BEGIN
BEGIN TRAN
	SELECT  NV_Dat,Ten_KH,DiaChi,DienThoai,NgayDat,ThanhTien,TinhTrang,GhiChu FROM PhieuDat WHERE Ma_PD=@Ma_PD
COMMIT TRAN
END
GO
--EXEC sp_SelectPhieuDatById'002'

CREATE PROCEDURE sp_CreatePhieuDatId
AS
BEGIN
		DECLARE @max NCHAR(3)
		SELECT @max = max(Ma_PD) FROM PhieuDat
		IF(@max is NULL)
			SELECT '001'
		ELSE
			BEGIN
			SET @max =  cast(@max AS INT)+1
			SELECT cast(@max AS NCHAR(3))
			END
END
GO
--EXEC sp_CreatePhieuDatId


--/////////////////////////////////////////////////////////////CHI TIET PHIEU DAT////////////////////////////////////////////

CREATE PROCEDURE sp_InsertChiTietPhieuDat
	@MaChiTietPD nchar(3),
	@Ma_PD nchar(3),
	@Ma_Mon nchar(3),
	@SoLuong int,
	@DonGia float,
	@ThanhTien float,
	@GhiChu nvarchar(100)
AS
BEGIN
BEGIN TRAN
	INSERT INTO ChiTietPhieuDat VALUES(@MaChiTietPD,@Ma_PD,@Ma_Mon,@SoLuong,@DonGia,@ThanhTien,@GhiChu)
COMMIT TRAN
END
GO

--EXEC sp_InsertChiTietPhieuDat'023','010','013',5,27000,135000,null

CREATE PROCEDURE sp_UpdateChiTietPhieuDatById
	@MaChiTietPD nchar(3),
	@Ma_PD nchar(3),
	@Ma_Mon nchar(3),
	@SoLuong int,
	@DonGia float,
	@ThanhTien float,
	@GhiChu nvarchar(100)
AS
BEGIN
BEGIN TRAN
	Update ChiTietPhieuDat SET Ma_PD=@Ma_PD,Ma_Mon=@Ma_Mon,SoLuong=@SoLuong,DonGia=@DonGia,ThanhTien=@ThanhTien,GhiChu=@GhiChu WHERE MaChiTietPD=@MaChiTietPD
COMMIT TRAN
END
GO

--EXEC sp_UpdateChiTietPhieuDatById'023','010','013',5,2700000,135000,null

CREATE PROCEDURE sp_DeleteChiTietPhieuDatById
	@MaChiTietPD nchar(3)
AS
BEGIN
BEGIN TRAN
	DELETE FROM ChiTietPhieuDat WHERE MaChiTietPD=@MaChiTietPD
COMMIT TRAN
END
GO

--EXEC sp_DeleteChiTietPhieuDatById '023'


CREATE PROCEDURE sp_SelectChiTietPhieuDatAll
AS
BEGIN
BEGIN TRAN
	SELECT MaChiTietPD,Ma_PD,Ma_Mon,SoLuong,DonGia,ThanhTien,GhiChu FROM ChiTietPhieuDat
COMMIT TRAN
END
GO

--EXEC sp_SelectChiTietPhieuDatAll

CREATE PROCEDURE sp_SelectChiTietPhieuDatById
	@MaChiTietPD nchar(3)
AS
BEGIN
BEGIN TRAN
	SELECT Ma_PD,Ma_Mon,SoLuong,DonGia,ThanhTien,GhiChu FROM ChiTietPhieuDat WHERE MaChiTietPD=@MaChiTietPD
COMMIT TRAN
END
GO

--EXEC sp_SelectChiTietPhieuDatById '022'



CREATE  PROCEDURE sp_CreateChiTietPhieuDatId
AS
BEGIN
		DECLARE @max NCHAR(3)
		SELECT @max = max(MaChiTietPD) FROM ChiTietPhieuDat
		IF(@max is NULL)
			SELECT '001'
		ELSE
			BEGIN
			SET @max =  cast(@max AS INT)+1
			SELECT cast(@max AS NCHAR(3))
			END
END
GO

--EXEC sp_CreateChiTietPhieuDatId





